package com.itany.corejava.code13_jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 石小俊
 * @date 2023年05月30日 9:18
 */
public class Test12_练习 {

    public static void main(String[] args) throws DataAccessException, SQLException {
    //    ClassBean classBean = new ClassBean("wbs23031", "Java");
    //    List<Student> students = new ArrayList<>();
    //    Student student = new Student("wbs23031001", "张三", 20);
    //    Student student2 = new Student("wbs23031002", "李四", 21);
    //    Student student3 = new Student("wbs23031003", "王五", 22);
    //    Student student4 = new Student("wbs23031004", "赵六", 23);
    //    students.add(student);
    //    students.add(student2);
    //    students.add(student3);
    //    students.add(student4);
    //    insertClassReturnPrimaryKey(classBean, students);


    //    List<Student> students = selectAllStudents();
    //    for (Student student : students) {
    //        System.out.println(student);
    //    }

        ClassBean classBean = selectClassDetailById(1);
        System.out.println(classBean);
    }

    /**
     * 保存班级以及该班中的所有学生信息
     * @param classBean
     * @throws DataAccessException
     */
    public static void insertClassReturnPrimaryKey(ClassBean classBean, List<Student> students) throws DataAccessException, SQLException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Integer id = null;
        try {
            conn = JDBCUtil.getConnection();
            conn.setAutoCommit(false);
            String sql = new StringBuffer()
                    .append(" insert into ")
                    .append(" t_class ")
                    .append("   (no,major)")
                    .append(" values ")
                    .append("   (?,?)")
                    .toString();
            ps = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
            ps.setString(1, classBean.getNo());
            ps.setString(2, classBean.getMajor());
            ps.executeUpdate();

            rs = ps.getGeneratedKeys();
            while (rs.next()) {
                id = rs.getInt(1);
            }
            classBean.setId(id);
            sql = new StringBuffer()
                    .append(" insert into ")
                    .append(" t_student ")
                    .append("   (no,name,age,class_id)")
                    .append(" values ")
                    .append("   (?,?,?,?)")
                    .toString();
            ps = conn.prepareStatement(sql);
            for (Student student : students) {
                student.setClassBean(classBean);
                ps.setString(1, student.getNo());
                ps.setString(2, student.getName());
                ps.setInt(3, student.getAge());
                ps.setInt(4, student.getClassBean().getId());
                // 执行DML操作
                ps.executeUpdate();
            }

            conn.commit();
        } catch (DataAccessException e) {
            conn.rollback();
            throw e;
        } catch (SQLException e) {
            conn.rollback();
            throw new DataAccessException("数据访问异常");
        } finally {
            JDBCUtil.close(conn, ps, rs);
        }
    }

    /**
     * 查询所有学生以及其所属班级信息
     * @return
     * @throws DataAccessException
     */
    public static List<Student> selectAllStudents() throws DataAccessException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Student> students = new ArrayList<>();
        try {
            conn = JDBCUtil.getConnection();
            String sql = new StringBuffer()
                    .append(" select s.id,s.no,s.name,s.age,c.id 'c.id',c.no 'c.no',c.major ")
                    .append(" from t_student s ")
                    .append(" left join t_class c ")
                    .append("   on s.class_id = c.id ")
                    .toString();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Student student = new Student();
                student.setId(rs.getInt("id"));
                student.setNo(rs.getString("no"));
                student.setName(rs.getString("name"));
                student.setAge(rs.getInt("age"));
                ClassBean classBean = new ClassBean();
                classBean.setId(rs.getInt("c.id"));
                classBean.setNo(rs.getString("c.no"));
                classBean.setMajor(rs.getString("major"));
                student.setClassBean(classBean);

                students.add(student);
            }
            return students;
        } catch (DataAccessException e) {
            throw e;
        } catch (SQLException e) {
            throw new DataAccessException("数据访问异常");
        } finally {
            JDBCUtil.close(conn, ps, rs);
        }
    }

    /**
     * 根据班级号查询指定班级以及该班级中所有学生信息
     * @param id
     * @return
     * @throws DataAccessException
     */
    public static ClassBean selectClassDetailById(Integer id) throws DataAccessException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ClassBean classBean = null;
        try {
            conn = JDBCUtil.getConnection();
            String sql = new StringBuffer()
                    .append(" select c.id,c.no,c.major,s.id 's.id',s.no 's.no',s.name,s.age ")
                    .append(" from t_class c  ")
                    .append(" left join t_student s ")
                    .append("   on c.id = s.class_id ")
                    .append(" where c.id = ? ")
                    .toString();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            rs = ps.executeQuery();
            while (rs.next()) {
                if (classBean == null) {
                    classBean = new ClassBean();
                    classBean.setId(rs.getInt("id"));
                    classBean.setNo(rs.getString("no"));
                    classBean.setMajor(rs.getString("major"));
                }
                Student student = new Student();
                student.setId(rs.getInt("s.id"));
                student.setNo(rs.getString("s.no"));
                student.setName(rs.getString("name"));
                student.setAge(rs.getInt("age"));
                classBean.addStudent(student);
            }
            return classBean;
        } catch (DataAccessException e) {
            throw e;
        } catch (SQLException e) {
            throw new DataAccessException("数据访问异常");
        } finally {
            JDBCUtil.close(conn, ps, rs);
        }
    }
}
